unit uProdTransf;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, uPai_new, StdCtrls, Menus, DB, Buttons, ExtCtrls, MMJPanel,
  FMTBcd, DBClient, Provider, SqlExpr, Grids, DBGrids, JvExDBGrids,
  JvDBGrid, Mask, JvExMask, JvToolEdit, JvBaseEdits;

type
  TfTransfProduto = class(TfPai_new)
    edMaterial: TEdit;
    Label1: TLabel;
    gridComp: TJvDBGrid;
    sqlComp: TSQLDataSet;
    dspComp: TDataSetProvider;
    cdsComp: TClientDataSet;
    dsComp: TDataSource;
    lblProd: TLabel;
    sqlLote: TSQLDataSet;
    dspLote: TDataSetProvider;
    cdsLote: TClientDataSet;
    dsLote: TDataSource;
    gridVerOf: TJvDBGrid;
    sqlOf: TSQLDataSet;
    dspOF: TDataSetProvider;
    cdsOf: TClientDataSet;
    dsOf: TDataSource;
    sqlCompOFL_CDCOMP: TStringField;
    cdsCompOFL_CDCOMP: TStringField;
    sqlCompPRO_DESCRI: TStringField;
    cdsCompPRO_DESCRI: TStringField;
    sqlOfPRO_DESCRI: TStringField;
    cdsOfPRO_DESCRI: TStringField;
    edDescri: TEdit;
    sqlProd: TSQLQuery;
    sqlLoteOFL_CODPRO: TStringField;
    sqlLoteOFL_CODLOT: TFMTBCDField;
    sqlLoteOFL_LOCEST: TStringField;
    sqlLoteOFL_CDCOMP: TStringField;
    sqlLotePRO_DESCRI: TStringField;
    cdsLoteOFL_CODPRO: TStringField;
    cdsLoteOFL_CODLOT: TFMTBCDField;
    cdsLoteOFL_LOCEST: TStringField;
    cdsLoteOFL_CDCOMP: TStringField;
    cdsLotePRO_DESCRI: TStringField;
    JvDBGrid1: TJvDBGrid;
    edDescriOf: TEdit;
    Label2: TLabel;
    PopupMenu2: TPopupMenu;
    ransferir1: TMenuItem;
    sqlOfOFL_CODPRO: TStringField;
    cdsOfOFL_CODPRO: TStringField;
    sspLog: TSQLStoredProc;
    sspLog2: TSQLStoredProc;
    edQtde: TJvCalcEdit;
    Label5: TLabel;
    sspTransf: TSQLStoredProc;
    edLocal: TEdit;
    Label6: TLabel;
    edJustificativa: TEdit;
    sqlCompRESERVA: TStringField;
    cdsCompRESERVA: TStringField;
    sqlOfRESERVA: TStringField;
    cdsOfRESERVA: TStringField;
    sqlLoteRESERVA: TStringField;
    cdsLoteRESERVA: TStringField;
    sqlLoteOFL_QTDEMP: TStringField;
    sqlLoteOFL_QTDCON: TStringField;
    cdsLoteOFL_QTDEMP: TStringField;
    cdsLoteOFL_QTDCON: TStringField;
    sqlOfTESTE: TFMTBCDField;
    cdsOfTESTE: TFMTBCDField;
    procedure btnProcurarClick(Sender: TObject);
    procedure gridVerOfCellClick(Column: TColumn);
    procedure edMaterialExit(Sender: TObject);
    procedure FormKeyPress(Sender: TObject; var Key: Char);
    procedure edOfDetKeyPress(Sender: TObject; var Key: Char);
    procedure btnGravarClick(Sender: TObject);
    procedure btnCancelarClick(Sender: TObject);
    procedure JvDBGrid1CellClick(Column: TColumn);
    procedure edQtdeKeyPress(Sender: TObject; var Key: Char);
    procedure edLocalExit(Sender: TObject);
  private
    procedure verLote;
    procedure verOF;
    procedure BuscaProdOf;
    procedure LogTransfLote;
    { Private declarations }
  public
    sMensagem: String;
    { Public declarations }
  end;

var
  fTransfProduto: TfTransfProduto;

implementation

uses udm;

{$R *.dfm}

{ TfPai_new1 }

procedure TfTransfProduto.verLote;
begin
  if (not cdsComp.Active) then
  begin
    MessageDlg('Selecione um Material.', mtError, [mbOK],0);
    exit;
  end;

  if (cdsLote.Active) then
    cdsLote.Close;
  cdsLote.CommandText := 'SELECT OFL_CODPRO, OFL_CODLOT, OFL_LOCEST, OFL_CDCOMP,' +
    ' TO_CHAR(OFL_QTDEMP -  OFL_QTDCON, ' + QuotedStr('FM999999999.99') +
    ' ) AS RESERVA, PRO_DESCRI, ' +
    ' TO_CHAR(OFL_QTDEMP, '  + QuotedStr('FM999999999.99') +
    ') OFL_QTDEMP, TO_CHAR(OFL_QTDCON, '  + QuotedStr('FM999999999.99') +
    ') OFL_QTDCON FROM   ATS_OFCONS_LOTE, F_PRODS' +
    ' WHERE ' +
    ' PRO_CODPRO = OFL_CDCOMP ' +
    ' AND (OFL_QTDEMP-OFL_QTDCON) > 0' +
    ' AND OFL_CDCOMP = ' + QuotedStr(cdsCompOFL_CDCOMP.AsString) +
    ' AND OFL_CODPRO = ' + QuotedStr(cdsOfOFL_CODPRO.AsString);
  cdsLote.Open;
end;

procedure TfTransfProduto.btnProcurarClick(Sender: TObject);
var str: string;
begin
  //inherited;
  str := '';
  if (edMaterial.Text = '') then
  begin
    MessageDlg('Informe um componente.', mtInformation , [mbOK],0);
    exit;
  end;
  str := ' AND OFL_CDCOMP = ' + QuotedStr(edMaterial.Text);
  if (cdsComp.Active) then
    cdsComp.Close;
  cdsComp.CommandText := 'SELECT OFL_CDCOMP,' +
    ' TO_CHAR(SUM(OFL_QTDEMP-OFL_QTDCON),' + QuotedStr('FM999999999.99') + ') AS RESERVA, PRO_DESCRI ' +
    ' FROM   ATS_OFCONS_LOTE, F_PRODS ' +
    ' WHERE  PRO_CODPRO = OFL_CDCOMP AND (OFL_QTDEMP-OFL_QTDCON) > 0 ' + str +
    ' GROUP BY OFL_CDCOMP, PRO_DESCRI';
  cdsComp.Open;
  verOf;
  verLote;
end;

procedure TfTransfProduto.verOF;
var sql: string;
begin
  sql := ' select ofl_codpro, pro_descri, TO_CHAR(SUM(OFL_QTDEMP-OFL_QTDCON)   , ' +
     QuotedStr('FM999999999.99') + ') AS RESERVA , ROUND(SUM(OFL_QTDEMP-OFL_QTDCON),3) as TESTE ' +
         ' from  ats_ofcons_lote, f_prods ' +
         ' where pro_codpro = ofl_codpro ' +
         ' AND   OFL_CDCOMP = '  + QuotedStr(cdsComp.Fields[0].AsString) +
         ' AND   OFL_QTDEMP-OFL_QTDCON > 0' +
         ' group by ofl_codpro, pro_descri';
  if (cdsOf.Active) then
    cdsOf.Close;
  cdsOf.CommandText := sql;
  cdsOf.Open;
end;

procedure TfTransfProduto.gridVerOfCellClick(Column: TColumn);
begin
  inherited;
  verLote;
end;

procedure TfTransfProduto.edMaterialExit(Sender: TObject);
begin
  inherited;
  if (edMaterial.Text <> '') then
  begin
    if (sqlProd.Active) then
      sqlProd.Close;
    sqlProd.SQL.Clear;
    sqlProd.SQL.Add('SELECT PRO_DESCRI FROM F_PRODS WHERE PRO_CODPRO = ' +
      QuotedStr(edMaterial.Text));
    sqlProd.Open;
    edDescri.Text := sqlProd.Fields[0].AsString;
    btnProcurar.Click;
    verOf;
    verLote;
  end;
end;

procedure TfTransfProduto.FormKeyPress(Sender: TObject; var Key: Char);
begin
  //inherited;
  if (key = #13) then
    btnProcurar.Click;
end;

procedure TfTransfProduto.edOfDetKeyPress(Sender: TObject; var Key: Char);
begin
 if (key = #13) then
 begin
   BuscaProdOf; 
   key:= #0;
   SelectNext((Sender as TwinControl),True,True);
 end;
end;

procedure TfTransfProduto.BuscaProdOf;
begin
  if (edLocal.Text <> '') then
  begin
    if (sqlProd.Active) then
      sqlProd.Close;
    sqlProd.SQL.Clear;
    sqlProd.SQL.Add('SELECT COF_CODPRO, PRO_DESCRI ' +
      ' FROM ATS_OF, ATS_DETOF, F_PRODS ' +
      ' WHERE DOF_CODIOF = COF_CODIOF AND PRO_CODPRO = COF_CODPRO AND COF_CODPRO = ' +
      edLocal.Text);
    sqlProd.Open;
    if (not sqlProd.IsEmpty) then
    begin
      edDescriOf.Text := sqlProd.Fields[1].AsString;
    end;
  end;

end;

procedure TfTransfProduto.btnGravarClick(Sender: TObject);
var Str:  String;
begin
  if (edLocal.Text = '') then
  begin
    MessageDlg('Informe o destino para o Componente.', mtInformation, [mbOK],0);
    exit;
  end;

  if ((cdsLote.IsEmpty) or (not cdsLote.Active)) then
  begin
    MessageDlg('Selecione o Lote a ser transferido.', mtInformation , [mbOK],0);
    exit;
  end;

  if (cdsOfOFL_CODPRO.AsString = edLocal.Text) then
  begin
    MessageDlg('Lote ja pertence a este Item.', mtWarning , [mbOK],0);
    exit;
  end;

  if ((edLocal.Text = 'AJUSTE') and (edJustificativa.Text = '')) then
  begin
    MessageDlg('Informe a Justificativa.', mtWarning , [mbOK],0);
    exit;
  end;


  {if (cdsLoteRESERVA.AsFloat < edQtde.Value) then
  begin
    MessageDlg('Quantidade maior que a disponivel.', mtWarning , [mbOK],0);
    exit;
  end;}

  if (sspTransf.Active) then
    sspTransf.Close;

  sspTransf.ParamByName('USUARIO').AsString   := dm.user;
  sspTransf.ParamByName('CDCOMP').AsString    := cdsLoteOFL_CDCOMP.AsString;
  sspTransf.ParamByName('ORIGEM').AsString    := cdsOfOFL_CODPRO.AsString;
  sspTransf.ParamByName('DESTINO').AsString   := edLocal.Text;
  sspTransf.ParamByName('JUSTIFICATIVA').AsString    := edJustificativa.Text;
  sspTransf.ParamByName('CODLOT').AsBcd       := cdsLoteOFL_CODLOT.asInteger;
  DecimalSeparator := '.';  
  sspTransf.ParamByName('QTDE').AsBcd         := edQtde.Value;
  DecimalSeparator := ',';
  try
  sspTransf.ExecProc;
  if (sspTransf.ParamByName('MENSAGEM').AsString = '') then
     MessageDlg('Lote transferido com sucesso.', mtWarning, [mbOK],0)
  else
    MessageDlg(sspTransf.ParamByName('MENSAGEM').AsString, mtWarning, [mbOK],0);
  sspTransf.Close;
  except
    on E: Exception do
    begin
      sMensagem := dm.TraduzErro(E.Message, sMensagem);
      if (sMensagem <> '') then
      begin
        MessageDlg(sMensagem, mtWarning, [mbOK],0);
      end
      else begin
        MessageDlg('Erro na Transferencia.', mtError, [mbOK],0);
      end;
    end;
  end;
   {
   // Verifica se esta OF Det. usa este Componte
   if (sqlProd.Active) then
     sqlProd.Close;
   sqlProd.SQL.Clear;
   sqlProd.SQL.Add('SELECT OFC_CODPRO FROM ATS_OFCONS, ATS_DETOFCONS ' +
     ' WHERE DFC_COFCON = OFC_CODIGO AND OFC_COFDET = ' + edOfDet.Text +
     ' AND DFC_CDCOMP = ' + QuotedStr(cdsLoteOFL_CDCOMP.AsString));
   sqlProd.Open;
   if (sqlProd.IsEmpty) then
   begin
     MessageDlg('Este componente nao pertence a esta O.F. Det.', mtError, [mbOK],0);
     exit;
   end;

   // Verifica se ja existe este Lote para este item
   if (sqlProd.Active) then
     sqlProd.Close;
   sqlProd.SQL.Clear;
   sqlProd.SQL.Add('SELECT OFL_CODPRO, OFL_CODLOT, OFL_LOCEST ' +
     ' FROM ATS_OFCONS_LOTE ' +
     ' WHERE OFL_CODPRO = ' + QuotedStr(edProdOf.Text) +
     ' AND OFL_CODLOT   = ' + IntToStr(cdsLoteOFL_CODLOT.asInteger) +
     ' AND OFL_LOCEST   = ' + QuotedStr(cdsLoteOFL_LOCEST.AsString));
   sqlProd.Open;

   // Executa a Transferencia
   // Insere o Lote Novo
   if (sqlProd.IsEmpty) then
   begin
     DecimalSeparator := '.';
     str := 'INSERT INTO ATS_OFCONS_LOTE VALUES('   +
       QuotedStr(edProdOf.Text) + ', ' +
       IntToStr(cdsLoteOFL_CODLOT.asInteger) + ', ' +
       QuotedStr(cdsLoteOFL_LOCEST.AsString) + ', ' +
       QuotedStr(cdsLoteOFL_CDCOMP.AsString) + ', ' +
       FloatToStr(edQtde.Value) + ', 0)';
     DecimalSeparator := ',';
   end
   else begin
     DecimalSeparator := '.';
     str := 'UPDATE ATS_OFCONS_LOTE SET '   +
       ' OFL_QTDEMP = OFL_QTDEMP + ' + FloatToStr(edQtde.Value) +
       ' WHERE OFL_CODPRO = ' + QuotedStr(edProdOf.Text) +
       ' AND OFL_CODLOT   = ' + IntToStr(cdsLoteOFL_CODLOT.asInteger) +
       ' AND OFL_LOCEST   = ' + QuotedStr(cdsLoteOFL_LOCEST.AsString);
     DecimalSeparator := ',';
   end;
   try
     dm.SQLConnection1.ExecuteDirect(str);
   except
     MessageDlg('Nao foi possivel transferir o item.', mtError, [mbOK],0);
     exit;
   end;
   // Da saida no lote anterior
   DecimalSeparator := '.';
   str := 'UPDATE ATS_OFCONS_LOTE SET ' +
     ' OFL_QTDEMP       = OFL_QTDEMP - ' + FloatToStr(edQtde.Value) +
     ' WHERE OFL_CODPRO = ' + QuotedStr(cdsLoteOFL_CODPRO.AsString) +
     ' AND OFL_CODLOT   = ' + IntToStr(cdsLoteOFL_CODLOT.asInteger) +
     ' AND OFL_LOCEST   = ' + QuotedStr(cdsLoteOFL_LOCEST.AsString);
   DecimalSeparator := ',';
   try
     dm.SQLConnection1.ExecuteDirect(str);
     LogTransfLote;
     MessageDlg('Lote transferido com sucesso.', mtInformation , [mbOK],0);
     edOfDet.Text    := '';
     edProdOf.Text   := '';
     edDescriOf.Text := '';
   except
     MessageDlg('Transferencia finalizada com ERRO.', mtError, [mbOK],0);
     exit;
   end;
   }
   verOf;
   verLote;

end;

procedure TfTransfProduto.btnCancelarClick(Sender: TObject);
begin
  //inherited;
  if (cdsComp.Active) then
    cdsComp.Close;
  if (cdsLote.Active) then
    cdsLote.Close;
  if (cdsOf.Active) then
    cdsOf.Close;
end;

procedure TfTransfProduto.LogTransfLote;
begin
  {// Log da Transf. das Etiquetas PRODUTO QUE SAIU O LOTE
  if (sspLog.Active) then
    sspLog.Close;
  sspLog.ParamByName('CODLOT').AsString := IntToStr(cdsLoteOFL_CODLOT.asInteger);
  sspLog.ParamByName('CODPRO').AsString := cdsLoteOFL_CODPRO.AsString;
  sspLog.ParamByName('LOCEST').AsString := cdsLoteOFL_LOCEST.AsString;;
  sspLog.ParamByName('LOGUSU').AsString := dm.user;
  DecimalSeparator := '.';
  sspLog.ParamByName('HISTOR').AsString := 'PRODUTO ANTERIOR: ' + cdsLoteOFL_CODPRO.AsString +
    ' QTDE: ' +  FloatToStr(cdsLoteOFL_QTDEMP.AsFloat) +
    ' - PRODUTO NOVO: ' + edProdOf.Text + ' QTDE: ' + FloatToStr(cdsLoteRESERVA.AsFloat);
  sspLog.ParamByName('QTDMOV').AsBcd := (-1)*cdsLoteRESERVA.AsFloat;
  DecimalSeparator := ',';

  // Log da Transf. das Etiquetas PRODUTO QUE ENTROU O LOTE
  if (sspLog2.Active) then
    sspLog2.Close;
  sspLog2.ParamByName('CODLOT').AsString := IntToStr(cdsLoteOFL_CODLOT.asInteger);
  sspLog2.ParamByName('CODPRO').AsString := edProdOf.Text;
  sspLog2.ParamByName('LOCEST').AsString := cdsLoteOFL_LOCEST.AsString;;
  sspLog2.ParamByName('LOGUSU').AsString := dm.user;
  DecimalSeparator := '.';
  sspLog2.ParamByName('HISTOR').AsString := 'PRODUTO ANTERIOR: ' + cdsLoteOFL_CODPRO.AsString +
    ' QTDE: ' +  FloatToStr(cdsLoteOFL_QTDEMP.AsFloat) +
    ' - PRODUTO NOVO: ' + edProdOf.Text + ' QTDE: ' + FloatToStr(cdsLoteRESERVA.AsFloat);
  sspLog2.ParamByName('QTDMOV').AsBcd := cdsLoteRESERVA.AsFloat;
  DecimalSeparator := ',';

  Try
    sspLog.ExecProc;
    sspLog2.ExecProc;
  except
    on E: Exception do
    begin
      sMensagem := dm.TraduzErro(E.Message, sMensagem);
      if (sMensagem <> '') then
      begin
        exit;
      end
      else begin
        sMensagem := 'ERRO REFUGO';
        exit;
      end;
    end;
  end;}
end;

procedure TfTransfProduto.JvDBGrid1CellClick(Column: TColumn);
begin
  //inherited;
  DecimalSeparator := '.';
  edQtde.Value := StrToFloat(cdsLoteRESERVA.AsString);
  DecimalSeparator := ',';  
end;

procedure TfTransfProduto.edQtdeKeyPress(Sender: TObject; var Key: Char);
begin
  //inherited;
  if (key = #13) then
  begin
    key:= #0;
    SelectNext((Sender as TwinControl),True,True);
  end;
end;

procedure TfTransfProduto.edLocalExit(Sender: TObject);
begin
  if (edLocal.Text <> '') then
  begin
    if (edLocal.Text = 'AJUSTE') then
    begin
      Label2.Caption := 'AJUSTE ESTOQUE.';
      Label2.Font.Color := clBlue;
      lblProd.Caption := 'Justificativa';
      edJustificativa.Visible := True;
      edDescriOf.Text := '';
    end
    else begin
      edJustificativa.Text := '';
    end;
  end;
end;

end.
